Spreadsheet Builder
The Spreadsheet Builder skill enables creation of professional Excel (.xlsx) and CSV files with advanced formatting, formulas, charts, and data analysis features. Using libraries like
exceljs
and
xlsx
, this skill handles everything from simple data exports to complex financial models and dashboards.
Generate data reports, financial statements, inventory lists, analysis dashboards, and any tabular data visualization. Support for multiple sheets, cell styling, conditional formatting, formulas, pivot tables, and charts makes this a comprehensive solution for spreadsheet automation.
Core Workflows
Workflow 1: Create Basic Excel Workbook
Purpose:
Build a simple Excel file with formatted data
Steps:
Import
exceljs
and create Workbook instance
Add worksheet with a name
Define columns with headers and widths
Add data rows
Apply basic formatting (fonts, colors, alignment)
Set column widths and row heights
Write to .xlsx file
Implementation:
const
ExcelJS
=
require
(
'exceljs'
)
;
async
function
createBasicWorkbook
(
data
,
outputPath
)
{
const
workbook
=
new
ExcelJS
.
Workbook
(
)
;
const
worksheet
=
workbook
.
addWorksheet
(
'Sales Data'
)
;
// Define columns
worksheet
.
columns
=
[
{
header
:
'Date'
,
key
:
'date'
,
width
:
12
}
,
{
header
:
'Product'
,
key
:
'product'
,
width
:
25
}
,
{
header
:
'Quantity'
,
key
:
'quantity'
,
width
:
10
}
,
{
header
:
'Price'
,
key
:
'price'
,
width
:
12
}
,
{
header
:
'Total'
,
key
:
'total'
,
width
:
12
}
]
;
// Style header row
worksheet
.
getRow
(
1
)
.
font
=
{
bold
:
true
,
size
:
12
}
;
worksheet
.
getRow
(
1
)
.
fill
=
{
type
:
'pattern'
,
pattern
:
'solid'
,
fgColor
:
{
argb
:
'FF4472C4'
}
}
;
worksheet
.
getRow
(
1
)
.
font
=
{
color
:
{
argb
:
'FFFFFFFF'
}
,
bold
:
true
}
;
// Add data
data
.
forEach
(
row
=>
{
worksheet
.
addRow
(
row
)
;
}
)
;
// Format currency columns
worksheet
.
getColumn
(
'price'
)
.
numFmt
=
'$#,##0.00'
;
worksheet
.
getColumn
(
'total'
)
.
numFmt
=
'$#,##0.00'
;
await
workbook
.
xlsx
.
writeFile
(
outputPath
)
;
}
Workflow 2: Add Formulas and Calculations
Purpose:
Create spreadsheets with automatic calculations and formulas
Steps:
Create workbook and worksheet
Add data columns
Insert formula cells (SUM, AVERAGE, IF, VLOOKUP, etc.)
Use cell references for dynamic calculations
Add conditional formulas
Create calculated columns
Add totals and subtotals
Implementation:
async
function
createWithFormulas
(
data
,
outputPath
)
{
const
workbook
=
new
ExcelJS
.
Workbook
(
)
;
const
worksheet
=
workbook
.
addWorksheet
(
'Financial Report'
)
;
worksheet
.
columns
=
[
{
header
:
'Month'
,
key
:
'month'
,
width
:
12
}
,
{
header
:
'Revenue'
,
key
:
'revenue'
,
width
:
15
}
,
{
header
:
'Expenses'
,
key
:
'expenses'
,
width
:
15
}
,
{
header
:
'Profit'
,
key
:
'profit'
,
width
:
15
}
,
{
header
:
'Margin %'
,
key
:
'margin'
,
width
:
12
}
]
;
// Add data rows
data
.
forEach
(
(
row
,
index
)
=>
{
const
rowIndex
=
index
+
2
;
// Account for header row
worksheet
.
addRow
(
{
month
:
row
.
month
,
revenue
:
row
.
revenue
,
expenses
:
row
.
expenses
,
profit
:
{
formula
:
B
${
rowIndex
}
-C
${
rowIndex
}
}
,
// Revenue - Expenses
margin
:
{
formula
:
D
${
rowIndex
}
/B
${
rowIndex
}
}
// Profit / Revenue
}
)
;
}
)
;
// Add totals row
const
lastRow
=
data
.
length
+
2
;
worksheet
.
addRow
(
{
month
:
'TOTAL'
,
revenue
:
{
formula
:
SUM(B2:B
${
lastRow
-
1
}
)
}
,
expenses
:
{
formula
:
SUM(C2:C
${
lastRow
-
1
}
)
}
,
profit
:
{
formula
:
SUM(D2:D
${
lastRow
-
1
}
)
}
,
margin
:
{
formula
:
D
${
lastRow
}
/B
${
lastRow
}
}
}
)
;
// Format totals row
worksheet
.
getRow
(
lastRow
)
.
font
=
{
bold
:
true
}
;
worksheet
.
getRow
(
lastRow
)
.
fill
=
{
type
:
'pattern'
,
pattern
:
'solid'
,
fgColor
:
{
argb
:
'FFE7E6E6'
}
}
;
// Number formatting
worksheet
.
getColumn
(
'revenue'
)
.
numFmt
=
'$#,##0.00'
;
worksheet
.
getColumn
(
'expenses'
)
.
numFmt
=
'$#,##0.00'
;
worksheet
.
getColumn
(
'profit'
)
.
numFmt
=
'$#,##0.00'
;
worksheet
.
getColumn
(
'margin'
)
.
numFmt
=
'0.00%'
;
await
workbook
.
xlsx
.
writeFile
(
outputPath
)
;
}
Workflow 3: Apply Conditional Formatting
Purpose:
Highlight cells based on rules and thresholds
Steps:
Create workbook with data
Define conditional formatting rules
Apply color scales for value ranges
Use data bars for visual comparison
Add icon sets for status indicators
Highlight top/bottom values
Apply custom formula-based rules
Implementation:
async
function
addConditionalFormatting
(
data
,
outputPath
)
{
const
workbook
=
new
ExcelJS
.
Workbook
(
)
;
const
worksheet
=
workbook
.
addWorksheet
(
'Performance'
)
;
// Add data...
worksheet
.
columns
=
[
{
header
:
'Employee'
,
key
:
'name'
,
width
:
20
}
,
{
header
:
'Sales'
,
key
:
'sales'
,
width
:
15
}
,
{
header
:
'Target'
,
key
:
'target'
,
width
:
15
}
,
{
header
:
'Performance'
,
key
:
'performance'
,
width
:
15
}
]
;
data
.
forEach
(
row
=>
worksheet
.
addRow
(
row
)
)
;
// Color scale: Green (high) to Red (low)
worksheet
.
addConditionalFormatting
(
{
ref
:
'B2:B100'
,
rules
:
[
{
type
:
'colorScale'
,
cfvo
:
[
{
type
:
'min'
}
,
{
type
:
'percentile'
,
value
:
50
}
,
{
type
:
'max'
}
]
,
color
:
[
{
argb
:
'FFF8696B'
}
,
// Red
{
argb
:
'FFFFEB84'
}
,
// Yellow
{
argb
:
'FF63BE7B'
}
// Green
]
}
]
}
)
;
// Data bars for performance column
worksheet
.
addConditionalFormatting
(
{
ref
:
'D2:D100'
,
rules
:
[
{
type
:
'dataBar'
,
minLength
:
0
,
maxLength
:
100
,
color
:
{
argb
:
'FF638EC6'
}
}
]
}
)
;
// Highlight values above target
worksheet
.
addConditionalFormatting
(
{
ref
:
'B2:B100'
,
rules
:
[
{
type
:
'expression'
,
formulae
:
[
'B2>C2'
]
,
// Sales > Target
style
:
{
fill
:
{
type
:
'pattern'
,
pattern
:
'solid'
,
bgColor
:
{
argb
:
'FFC6EFCE'
}
}
}
}
]
}
)
;
await
workbook
.
xlsx
.
writeFile
(
outputPath
)
;
}
Workflow 4: Create Charts and Visualizations
Purpose:
Add charts to visualize data trends and comparisons
Steps:
Create workbook with data
Add data worksheet
Create chart worksheet or embed in data sheet
Define chart type (bar, line, pie, scatter, etc.)
Set data ranges for series
Configure chart title, axes, legend
Apply styling and colors
Position chart on worksheet
Implementation:
async
function
createWithChart
(
data
,
outputPath
)
{
const
workbook
=
new
ExcelJS
.
Workbook
(
)
;
const
worksheet
=
workbook
.
addWorksheet
(
'Sales'
)
;
// Add data
worksheet
.
columns
=
[
{
header
:
'Month'
,
key
:
'month'
,
width
:
12
}
,
{
header
:
'Sales'
,
key
:
'sales'
,
width
:
15
}
]
;
data
.
forEach
(
row
=>
worksheet
.
addRow
(
row
)
)
;
// Create chart (Note: exceljs has limited chart support, consider using xlsx-chart)
// For full chart support, you may need to use Excel templates or officegen library
// Alternative: Add chart using worksheet image
// Or use a charting library to generate image, then embed
await
workbook
.
xlsx
.
writeFile
(
outputPath
)
;
}
// For advanced charts, consider using officegen or generating chart images
Workflow 5: Multi-Sheet Workbook with Links
Purpose:
Create complex workbooks with multiple related sheets
Steps:
Create workbook
Add multiple worksheets (Summary, Details, Raw Data, etc.)
Create cross-sheet formulas and references
Add hyperlinks between sheets
Protect sheets with passwords
Hide/show sheets as needed
Set active sheet and freeze panes
Implementation:
async
function
createMultiSheetWorkbook
(
data
,
outputPath
)
{
const
workbook
=
new
ExcelJS
.
Workbook
(
)
;
// Summary sheet
const
summary
=
workbook
.
addWorksheet
(
'Summary'
)
;
summary
.
columns
=
[
{
header
:
'Metric'
,
key
:
'metric'
,
width
:
25
}
,
{
header
:
'Value'
,
key
:
'value'
,
width
:
15
}
]
;
summary
.
addRow
(
{
metric
:
'Total Sales'
,
value
:
{
formula
:
"SUM(Details!B:B)"
}
}
)
;
summary
.
addRow
(
{
metric
:
'Average Order'
,
value
:
{
formula
:
"AVERAGE(Details!B:B)"
}
}
)
;
summary
.
addRow
(
{
metric
:
'Total Orders'
,
value
:
{
formula
:
"COUNTA(Details!A:A)-1"
}
}
)
;
// Details sheet
const
details
=
workbook
.
addWorksheet
(
'Details'
)
;
details
.
columns
=
[
{
header
:
'Order ID'
,
key
:
'id'
,
width
:
12
}
,
{
header
:
'Amount'
,
key
:
'amount'
,
width
:
15
}
,
{
header
:
'Date'
,
key
:
'date'
,
width
:
12
}
]
;
data
.
forEach
(
row
=>
details
.
addRow
(
row
)
)
;
// Freeze header row
details
.
views
=
[
{
state
:
'frozen'
,
xSplit
:
0
,
ySplit
:
1
}
]
;
// Add hyperlink from summary to details
summary
.
getCell
(
'A1'
)
.
value
=
{
text
:
'View Details'
,
hyperlink
:
'#Details!A1'
,
tooltip
:
'Jump to Details sheet'
}
;
summary
.
getCell
(
'A1'
)
.
font
=
{
color
:
{
argb
:
'FF0000FF'
}
,
underline
:
true
}
;
// Set Summary as active sheet
summary
.
state
=
'visible'
;
details
.
state
=
'visible'
;
await
workbook
.
xlsx
.
writeFile
(
outputPath
)
;
}
Workflow 6: Export to CSV
Purpose:
Create simple CSV files for data exchange
Steps:
Format data as array of objects or arrays
Define headers if needed
Convert to CSV format
Handle special characters and quotes
Set delimiter (comma, semicolon, tab)
Write to file with proper encoding
Implementation:
const
fs
=
require
(
'fs'
)
;
function
createCSV
(
data
,
outputPath
,
options
=
{
}
)
{
const
delimiter
=
options
.
delimiter
||
','
;
const
headers
=
options
.
headers
||
Object
.
keys
(
data
[
0
]
)
;
// Create header row
let
csv
=
headers
.
join
(
delimiter
)
+
'\n'
;
// Add data rows
data
.
forEach
(
row
=>
{
const
values
=
headers
.
map
(
header
=>
{
let
value
=
row
[
header
]
||
''
;
// Escape quotes and wrap in quotes if contains delimiter or newline
if
(
typeof
value
===
'string'
&&
(
value
.
includes
(
delimiter
)
||
value
.
includes
(
'\n'
)
||
value
.
includes
(
'"'
)
)
)
{
value
=
'"'
+
value
.
replace
(
/
"
/
g
,
'""'
)
+
'"'
;
}
return
value
;
}
)
;
csv
+=
values
.
join
(
delimiter
)
+
'\n'
;
}
)
;
fs
.
writeFileSync
(
outputPath
,
csv
,
'utf8'
)
;
}
Quick Reference
Action
Command/Trigger
Create Excel workbook
"create excel file with [data]"
Generate CSV
"export [data] to csv"
Add formulas
"add formulas to spreadsheet"
Apply formatting
"format excel cells [style]"
Create chart
"add chart to workbook"
Multi-sheet workbook
"create workbook with [sheets]"
Conditional formatting
"apply conditional formatting"
Freeze panes
"freeze header row"
Protect sheet
"password protect [sheet]"
Best Practices
Data Validation:
Validate data types before writing to cells
Number Formatting:
Apply appropriate number formats (currency, percentage, date)
Column Widths:
Set widths based on content for readability
Freeze Panes:
Freeze header rows for scrollable data
Named Ranges:
Use named ranges for formulas in complex workbooks
Templates:
Create templates for repeated report types
Memory Management:
Use streaming for very large datasets (>100k rows)
Error Handling:
Wrap formula creation in try-catch for invalid references
CSV Encoding:
Use UTF-8 BOM for international characters
Performance:
Batch cell operations rather than individual cell writes
Testing:
Verify formulas calculate correctly after file creation
Documentation:
Comment complex formulas within cells
Common Patterns
Inventory Report:
worksheet
.
columns
=
[
{
header
:
'SKU'
,
key
:
'sku'
,
width
:
15
}
,
{
header
:
'Product'
,
key
:
'product'
,
width
:
30
}
,
{
header
:
'Quantity'
,
key
:
'qty'
,
width
:
12
}
,
{
header
:
'Unit Price'
,
key
:
'price'
,
width
:
15
}
,
{
header
:
'Total Value'
,
key
:
'value'
,
width
:
15
}
]
;
data
.
forEach
(
(
item
,
idx
)
=>
{
const
row
=
idx
+
2
;
worksheet
.
addRow
(
{
sku
:
item
.
sku
,
product
:
item
.
product
,
qty
:
item
.
qty
,
price
:
item
.
price
,
value
:
{
formula
:
C
${
row
}
*D
${
row
}
}
}
)
;
}
)
;
Financial Dashboard:
// Summary sheet with KPIs
summary
.
addRow
(
{
metric
:
'Revenue'
,
value
:
{
formula
:
"SUM(Data!B:B)"
}
}
)
;
summary
.
addRow
(
{
metric
:
'Expenses'
,
value
:
{
formula
:
"SUM(Data!C:C)"
}
}
)
;
summary
.
addRow
(
{
metric
:
'Net Profit'
,
value
:
{
formula
:
"B2-B3"
}
}
)
;
summary
.
addRow
(
{
metric
:
'Profit Margin'
,
value
:
{
formula
:
"B4/B2"
}
}
)
;
summary
.
getColumn
(
'value'
)
.
numFmt
=
'$#,##0.00'
;
Dependencies
Install required packages:
npm
install
exceljs
npm
install
xlsx
Alternative library
npm install csv-writer
For CSV generation
Error Handling Invalid Formulas: Validate formula syntax before assignment Cell References: Ensure referenced cells exist File Permissions: Handle write errors gracefully Memory Limits: Use streaming mode for files >50MB Data Types: Coerce data to appropriate types (number, string, date) Encoding Issues: Ensure UTF-8 encoding for international characters Performance Tips Use worksheet.addRows() instead of multiple addRow() calls Set column properties before adding data Avoid reading cells unnecessarily Use streaming write for large datasets Batch style operations Pre-calculate values instead of formulas when possible for static data Advanced Features Streaming Large Files: const workbook = new ExcelJS . stream . xlsx . WorkbookWriter ( { filename : outputPath } ) ; const worksheet = workbook . addWorksheet ( 'Large Data' ) ; // Add data in chunks worksheet . commit ( ) ; workbook . commit ( ) ; Data Validation: worksheet . getCell ( 'A2' ) . dataValidation = { type : 'list' , allowBlank : true , formulae : [ '"Option1,Option2,Option3"' ] } ; Protection: await worksheet . protect ( 'password' , { selectLockedCells : true , selectUnlockedCells : true } ) ;